package com.jeasonzhao.report.engine;

import com.jeasonzhao.commons.basic.StringCollection;
import com.jeasonzhao.commons.db.SqlParameter;
import com.jeasonzhao.commons.parser.lex.LexException;
import com.jeasonzhao.commons.parser.lex.LexToken;
import com.jeasonzhao.commons.parser.lex.LexTokenCollection;
import com.jeasonzhao.commons.parser.lex.LexTokenTypes;
import com.jeasonzhao.commons.parser.lex.SqlTokenizer;
import com.jeasonzhao.commons.utils.Algorithms;
import com.jeasonzhao.commons.utils.DataTypes;
import com.jeasonzhao.report.exceptions.ReportException;
import com.jeasonzhao.report.exceptions.ReportRuntimeException;
import com.jeasonzhao.report.model.Report;
import com.jeasonzhao.report.model.ReportParam;
import com.jeasonzhao.report.model.ReportParamCollection;
import com.jeasonzhao.report.exceptions.ReportParameterException;
import com.jeasonzhao.report.exceptions.SQLReportException;

public class PrepareSQL
{
    private PrepareSQL()
    {
    }

    private static LexTokenCollection getParamterFromSQL(LexTokenCollection initSqlTokens)
    {
        LexTokenCollection ret = new LexTokenCollection();
        java.util.HashSet<String> set = new java.util.HashSet<String>();
        for(int n = 0;null != initSqlTokens && n < initSqlTokens.size();n++)
        {
            LexToken token = initSqlTokens.get(n);
            if(SqlTokenizer.isSQLParameterToken(token) && set.contains(token.getToken().toLowerCase()) == false)
            {
                set.add(token.getToken().toLowerCase());
                ret.add(token);
            }
        }
        return ret;
    }

    private static String getUnmappedParameterNames(LexTokenCollection params
        ,ReportParamCollection parameters)
    {
        if(null == params || params.size() < 1)
        {
            return null;
        }
        String strRet = null;
        for(LexToken t : params)
        {
            if(t == null || Algorithms.isEmpty(t.getToken()))
            {
                continue;
            }
            if(null == parameters ||
               null == parameters.findParameterByName(t.getToken(),false))
            {
                if(null == strRet)
                {
                    strRet = t.getToken();
                }
                else if(strRet.length() > 0)
                {
                    strRet += "," + t.getToken();
                }
            }
        }
        return strRet;
    }

    private static ReportParamCollection cordinateParameters(Report report
        ,LexTokenCollection initSqlToken
        ,ReportParamCollection reportParameters)
        throws ReportException
    {
        if(null == reportParameters)
        {
            reportParameters = new ReportParamCollection();
        }
        LexTokenCollection sqlParameters = getParamterFromSQL(initSqlToken);
        if(null == sqlParameters || sqlParameters.size() < 1)
        {
            return null; //No SQL parameters, set the return value to NULL
        }
        //Check the un-mapped SQL parameters
        String strUnmappedParameters = getUnmappedParameterNames(sqlParameters,reportParameters);
        if(Algorithms.notEmpty(strUnmappedParameters))
        {
            throw new ReportException.UnmappedParameter(report,strUnmappedParameters);
        }
        //reportParameters should not be NULL after getUnmappedParameterNames()
        reportParameters.removeDupliactedItems();
        //Remove the report-parameters which not in SQL parameters
        for(int n = 0;n < reportParameters.size();n++)
        {
            if(sqlParameters.find(reportParameters.get(n).getParam(),false) < 0)
            {
                reportParameters.remove(n);
                n--;
            }
        }
        return null == reportParameters || reportParameters.size() < 1 ? null : reportParameters;
    }

    public static SqlPreparation prepareSqlDotNetStyle(Report report,String strSql,ReportParamCollection ps,
        char prefix)
        throws SQLReportException
    {
        return prepareSql(report,strSql,ps,true,prefix);
    }

    public static SqlPreparation prepareSqlJavaStyle(Report report,String strSql,ReportParamCollection ps)
        throws SQLReportException
    {
        return prepareSql(report,strSql,ps,false,SqlTokenizer.END);
    }

    public static SqlPreparation prepareSql(Report report,String strSql,ReportParamCollection ps
                                            ,boolean isKeepParamNameInSql
                                            ,char prefix)
        throws SQLReportException
    {
        if(Algorithms.isEmpty(strSql))
        {
            return null;
        }
        try
        {
            LexTokenCollection initSqlTokens = (new SqlTokenizer(strSql)).getTokens(false,false);
            ps = cordinateParameters(report,initSqlTokens,ps);
            if(null == ps || ps.size() < 1 || initSqlTokens.size() < 1)
            {
                return new SqlPreparation(initSqlTokens.toString());
            }
            StringCollection newTokens = new StringCollection();
            java.util.ArrayList<SqlParameter> destParameters = new java.util.ArrayList<SqlParameter>();
            for(int nToken = 0;nToken < initSqlTokens.size();nToken++)
            {
                LexToken token = initSqlTokens.elementAt(nToken);
                if(SqlTokenizer.isSQLParameterToken(token))
                {
                    ReportParam param = checkParameter(report,ps,token.getToken());
                    if(param.isReplaceValue())
                    {
                        newTokens.add(token.replaceToken(param.getValues() == null ? "" : param.getValues().toString(",",false)));
                    }
                    else if(param.isOptional() && param.hasBeenSetValues() == false)
                    {
                        //Remove the optional parameters
                        if(nToken <= 0)
                        {
                            throw new ReportException.OptParamAtBegin(report,param,token);
                        }
                        nToken = removeOptionalParameterFromSQL(report,initSqlTokens,newTokens,nToken,token);
                    }
                    else if(param.isSingleSelect() == false && param.valueSize() > 1)
                    {
                        //Multi selected parameters
                        if(nToken <= 0)
                        {
                            throw new ReportException.MultiParamAtBegin(report,param,token);
                        }
                        nToken = expandMultiValueParameterSql(report,initSqlTokens,newTokens
                            ,destParameters,nToken,token,param,isKeepParamNameInSql,prefix);
                    }
                    else
                    {
                        String strNewName = getParameterName(param.getParam(),isKeepParamNameInSql,prefix);
                        newTokens.add(strNewName);
                        destParameters.add(generateSqlParameter(param,param.getSingleParamValue(),strNewName));
                    }
                }
                else
                {
                    newTokens.add(token.replaceToken(null));
                }
            }
            SqlPreparation ret = new SqlPreparation(newTokens.toString(" ",false));
            ret.setParameters(destParameters);
            return ret;
        }
        catch(ReportException ex)
        {
            throw ex;
        }
        catch(ReportRuntimeException ex)
        {
            throw ex;
        }
        catch(LexException ex)
        {
            throw new ReportException.SqlParseError(report);
        }
    }

    private static int m_nParameterIndex = 0;
    private static String getParameterName(String strName,boolean isKeepingName,char prefix)
    {
        if(Algorithms.isEmpty(strName))
        {
            strName = "?";
        }
        if(isKeepingName)
        {
            strName = strName.trim();
            if(SqlTokenizer.END == prefix)
            {
                prefix = '@'; //Default TSQL
            }
            if(strName.equals("?"))
            {
                m_nParameterIndex++;
                return prefix + "auto_generate_param_name_" + m_nParameterIndex;
            }
            else
            {
                if(SqlTokenizer.isSQLParameterToken(strName))
                {
                    strName = strName.substring(1);
                }
                return prefix + strName;
            }
        }
        else
        {
            return "?"; //Java style
        }
    }

    private static SqlParameter generateSqlParameter(ReportParam param,Object value,String strNewParameterName)
    {
        SqlParameter p = new SqlParameter(param.getParam());
        p.setName(strNewParameterName);
        int nType = SqlParameter.getTypeFromClass(param.getDataType().getInitType());
        if(null == value)
        {
            p.setDbNull(nType);
        }
        else
        {
            p.setValue(value,nType);
        }
        return p;
    }

    private static int removeOptionalParameterFromSQL(Report report,LexTokenCollection initSqlTokens
        ,StringCollection newTokens
        ,int nToken,LexToken token)
        throws ReportException
    {
        //get the condition operations
        int nBracketCount = 0;
        for(int n = nToken - 1;n >= 0;n--)
        {
            LexToken tkPrev = initSqlTokens.elementAt(n);
            nBracketCount += tkPrev.equals("(") && tkPrev.getType() == LexTokenTypes.SPECIAL ? 1 : 0;
            boolean bBreak = tkPrev.isName()
                && tkPrev.getToken().equalsIgnoreCase("in") == false
                && tkPrev.getToken().equalsIgnoreCase("like") == false
                && tkPrev.getToken().equalsIgnoreCase("not") == false;
            if(bBreak)
            {
                newTokens.removeTail();
                break;
            }
            newTokens.removeTail();
        }
        //Remove the end brackets
        while(nBracketCount > 0 && nToken < initSqlTokens.size() - 1)
        {
            nToken++;
            LexToken tkPrev = initSqlTokens.elementAt(nToken);
            if(tkPrev.equals(")") && tkPrev.getType() == LexTokenTypes.SPECIAL)
            {
                nBracketCount--;
            }
        }
        //Find the name and bracket
        for(;nToken < initSqlTokens.size();nToken++)
        {
            LexToken tkPrev = initSqlTokens.elementAt(nToken);
            if(((tkPrev.equals(")") || tkPrev.equals("(")) && tkPrev.getType() == LexTokenTypes.SPECIAL) || tkPrev.isName())
            {
                break;
            }
        }
        if(nBracketCount > 0)
        {
            throw new ReportException.UnmatchedBracket(report,token);
        }
        newTokens.add("1=1");
        return nToken;
    }

    private static int expandMultiValueParameterSql(Report report,LexTokenCollection initSqlTokens,
        StringCollection newTokens,java.util.ArrayList<SqlParameter> destParameters,
        int nToken,LexToken token,ReportParam param,boolean keepName,char prefix)
        throws ReportException
    {
        LexToken tkPrev = initSqlTokens.elementAt(nToken - 1);
        if(tkPrev.equals("(") && tkPrev.getType() == LexTokenTypes.SPECIAL)
        {
            newTokens.removeTail();
            tkPrev = initSqlTokens.elementAt(nToken - 2);
            //?????????????????????
            if(nToken + 1 < initSqlTokens.size() && initSqlTokens.elementAt(nToken + 1).equals(")"))
            {
                nToken++;
            }
        }
        newTokens.remove(newTokens.size() - 1);
//        if(tkPrev.getType() != LexTokenTypes.NAME && tkPrev.getType() != LexTokenTypes.SPECIAL)
//        {
//            throw new ReportException.MultiParamOperatorError(report,token,tkPrev);
//        }
        String strPrev = null;
        String strPost = null;
        if(tkPrev.getToken().equalsIgnoreCase("=")
           || tkPrev.getToken().equalsIgnoreCase("==")
           || tkPrev.getToken().equalsIgnoreCase("in"))
        {
            strPrev = "in(";
            strPost = ")";
        }
        else if(tkPrev.getToken().equalsIgnoreCase("<>")
                || tkPrev.getToken().equalsIgnoreCase("!="))
        {
            strPrev = "not in(";
            strPost = ")";
        }
        else
        {
            throw new ReportException.MultiParamOperatorError(report,token,tkPrev);
        }
        newTokens.add(strPrev);
        for(int nx = 0;nx < param.valueSize();nx++)
        {
            if(nx > 0)
            {
                newTokens.add(",");
            }
            String strNewName = getParameterName(null,keepName,prefix);
            newTokens.add(strNewName);
            destParameters.add(generateSqlParameter(param,param.getValue(nx),strNewName));
        }
        newTokens.add(strPost);
        return nToken;
    }

    private static ReportParam checkParameter(Report report,ReportParamCollection ps,String strSqlParameterName)
        throws SQLReportException
    {
        ReportParam param = ps.findParameterByName(strSqlParameterName);
        if(null == param)
        {
            throw new ReportException.UnmappedParameter(report,strSqlParameterName);
        }
        if((false == param.isOptional() || param.isFixedValue()) && false == param.hasBeenSetValues())
        {
            throw new ReportParameterException.ParameterNotSet(report,param);
        }
        return param;
    }

    public static void main(String[] argvs)
        throws Exception
    {
        ReportParamCollection collections = new ReportParamCollection();
        ReportParam p = new ReportParam();
        p.setParam("@Date");
        p.setIsSingleSelect(false);
        p.setIsOptional(true);
        p.setDataType(DataTypes.DATE);
        p.setValue(new java.util.Date(),new java.util.Date(),new java.util.Date());
        collections.add(p);
        p = new ReportParam();
        p.setParam("@endDate");
        p.setIsOptional(true);
        p.setDataType(DataTypes.DATE);
        p.setValue(new java.util.Date(),new java.util.Date(),new java.util.Date());
        collections.add(p);
        ////////////////////////////////////////////////////////////////////////
        String strSql =
//            "\n"+" select a.kpiid kpiid,cityid,kpidate,kpivalue,kpivalue2,kpivalue3,kpivalue4" +
//            "\n"+" from kpi_values a,kpi_define b" +
//            "\n"+" where b.kpiid=a.kpiid" +
//            "\n"+" and a.kpiid<2" +
//            "\n"+" and a.cityid >=1 and a.cityId<=10" +
            "\n" + " Aand a.kpidate1 not in(@Date) " +
            "\n" + " Band a.kpidate2 !=(@Date) and project_name like ('%'||'%')" +
            "\n" + " Cand a.kpidate3 <>@Date " +
            "\n" + " Dand a.kpidate4 <=@endDate";
        SqlPreparation ps = PrepareSQL.prepareSqlDotNetStyle(null,strSql,collections,':');
        System.out.println(ps.getSql());
    }

}
